package test.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Session {
	
	static String url = "jdbc:mysql://10.167.202.83:3306/mytest?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false";
	static String user = "root";
	static String password = "root";
	static Connection conn = null;
	
	public Session() {
		conn = initConnection();
	}
	
	public List select(String sql, Object[] params) {
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			if(params!=null && params.length > 0) {
				for(int i=0;i<params.length;i++) {
					ps.setObject(i+1, params[i]);
				}
			}
			ResultSet rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int total = rsmd.getColumnCount();
			List list = new ArrayList();
			while(rs.next()) {
				Map map = new HashMap();
				for(int i=0;i<total;i++) {
					map.put(rsmd.getColumnName(i), rs.getObject(i));
				}
				list.add(map);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public void update(String sql, Object[] params) {
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			if(params!=null && params.length > 0) {
				for(int i=0;i<params.length;i++) {
					ps.setObject(i+1, params[i]);
				}
			}
			ps.setQueryTimeout(5);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public Connection initConnection() {
		try {
			Connection conn = DriverManager.getConnection(url, user, password);
			conn.setAutoCommit(false);
			return conn;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public void closeConnection() {
		try {
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
}
